*******************************************************************
*Replication code for
*	Article: COVID 19 infection induces higher trust in strangers
*	Journal: PNAS
*	Authors: Diego Gambetta and Davide Morisi
*******************************************************************


*********************
*NOTE: You can run the code below only with access to information about the respondents' municipality of residence
*This information cannot be released publicly for privacy reasons
*If you are interested in this information, please contact the authors
*********************

/*

*Merging municipality-level data with individual-level data from SWG

*Set working directory
cd "[your working directory]"

*Open SWG dataset
use "Replication\Privacy\swg_panel_replication_municip", clear


************
*Assign new code to two municipalities that later on merged into the same one
replace municip="078157" if municip=="078044" 
replace municip="078157" if municip=="078108" 


*Changing old istat code in SWG dataset
clonevar municip_mar1 = municip
*1. Torino	001297 > vico canavese > diventa Valchiusa 001318
replace municip_mar1 ="001318" if municip_mar1=="001297"
*2. Como	013019 > bellagio > nuovo codice istat: 013250
replace municip_mar1 ="013250" if municip_mar1=="013019"
*3. Como	013109 > comune di gironico > soppresso nel 2014, diventa parte del comune di colverde > nuovo codice istat: 013251
replace municip_mar1 ="013251" if municip_mar1=="013109"
*4. Como	013194 > Ramponio Verna, soppresso, diventa Alta Valle Intelvi > nuovo covice 013253
replace municip_mar1 ="013253" if municip_mar1=="013194"
*5. Trento	022082 > Faver, soppresso, diventa Altavalle, nuovo codice 022235
replace municip_mar1 ="022235" if municip_mar1=="022082"
*6. Vicenza	024093 > Valbrenta,	024125
replace municip_mar1 ="024125" if municip_mar1=="024093"
*7. Belluno	025041 > Alpago,	025072
replace municip_mar1 ="025072" if municip_mar1=="025041"
*8. Ferrara	038020 > Riva del Po,	038029
replace municip_mar1 ="038029" if municip_mar1=="038020"
*9. Pesaro	Urb 041050 > Colli al Metauro,	041069
replace municip_mar1 ="041069" if municip_mar1=="041050"
*10. Biella	096070 >  Valdilana 096088
replace municip_mar1 ="096088" if municip_mar1=="096070"
*11. Ogliastra	105019 > Triei, 091097
replace municip_mar1 ="091097" if municip_mar1=="105019"
*12. Medio	Camp 106001 > arbus, 111001
replace municip_mar1 ="111001" if municip_mar1=="106001"
*13. Medio	Camp 106022 > tuili, 111091
replace municip_mar1 ="111091" if municip_mar1=="106022"
*14. Carbonia-I	107016 > portoscuso, 111057
replace municip_mar1 ="111057" if municip_mar1=="107016"
*15. Carbonia-I	107020 > sant'antioco, 111071
replace municip_mar1 ="111071" if municip_mar1=="107020"

********************************
*Merge with mortatlity data from ISTAT
*Original data downloaded from : https://www.istat.it/it/archivio/240401 

merge m:1 municip_mar1 using "Replication\Privacy\Tavola riepilogativa_31DICEMBRE_7.903COMUNI_r.dta"
*all merged from master
drop if _merge==2 // municipalities not in SWG data

*past average deaths
rename _19_jan  death_1519_jan
rename _19_feb  death_1519_feb
rename _19_mar  death_1519_mar
rename _19_apr  death_1519_apr
rename _19_may  death_1519_may
rename _19_jun  death_1519_jun
rename _19_jul  death_1519_jul
rename _19_aug  death_1519_aug
rename _19_sep  death_1519_sep
rename _19_oct  death_1519_oct
rename _19_nov  death_1519_nov
rename _19_dec  death_1519_dec

*deaths in 2020
rename _jan death_2020_jan
rename _feb  death_2020_feb
rename _mar  death_2020_mar
rename _apr  death_2020_apr
rename _may  death_2020_may
rename _jun  death_2020_jun
rename _jul  death_2020_jul
rename _aug  death_2020_aug
rename _sep  death_2020_sep
rename _oct  death_2020_oct
rename _nov  death_2020_nov
rename _dec  death_2020_dec
*deaths in 2021
rename deaths_jan21 death_2021_jan
*past average in january including 2020
gen death_1520_jan = (death_1519_jan+death_2020_jan)/2

*percentage increases in 2 months periods
*average excess death in march & april 2020
gen incr_mar_apr2020 = (((death_2020_mar+death_2020_apr) - (death_1519_mar+death_1519_apr))*100)/(death_1519_mar+death_1519_apr)
*average excess death in september & october 2020
gen incr_sep_oct2020 = (((death_2020_oct+death_2020_sep) - (death_1519_oct+death_1519_sep))*100)/(death_1519_oct+death_1519_sep)
*average excess death in december 2020 and january 2021
gen incr_dec_jan2021 = (((death_2020_dec+death_2021_jan) - (death_1519_dec+death_1520_jan))*100)/(death_1519_dec+death_1520_jan)
replace incr_dec_jan2021=0 if incr_dec_jan2021==.
sum incr_mar_apr2020 incr_sep_oct2020 incr_dec_jan2021

*quantiles
xtile incr_mar_apr2020_quin = incr_mar_apr2020, nq(5)
xtile incr_sep_oct2020_quin = incr_sep_oct2020, nq(5)
xtile incr_dec_jan2021_quin = incr_dec_jan2021, nq(5)


******************
*Merge with population data from ISTAT
clonevar municip_mar = municip
drop _merge
merge m:1 municip_mar using "Replication\Privacy\istat01", force
drop if _merge==2

*people aged more than 65
gen pop2018_65plus = pop2018-pop2018_age_0_65

*share of people aged 65+
gen share65plus = pop2018_65plus/pop2018
xtile share65plus_dec = share65plus, nq(10)

*ratio of males over females
gen male_ratio = pop2018_male / pop2018_female
sum male_ratio, d // the higher, the more males compared to females
xtile male_ratio_dec = male_ratio, nq(10)


*********************
*Merge with additional population data from ISTAT
drop _merge
merge m:1 municip_mar using "Replication\Privacy\istat02.dta", force
drop if _merge==2

*generate population density 2018
gen dens2018 = pop2018/surface_km2
sum dens2018, d // people per km2

*logged transformations
gen log_pop2018 = log(pop2018)
gen log_dens2018 = log(dens2018)


*******************
*Merge with employment rate from Il Sole 24 ore

*Retrieved on 8 June 2020, from www.infodata.ilsole24ore.com/2019/04/16/39185/

*import excel "Data\ISTAT\dati_occupazione_sole24ore_copia.xlsx", firstrow clear
*distinct municip_mar
*save "Data\ISTAT\dati_occupazione_sole24ore.dta", replace

*use "Data\SWG_recoded_istat3.dta", clear
drop _merge
merge m:1 municip_mar using "Replication\Privacy\dati_occupazione_sole24ore_r.dta", force
drop if _merge==2

*employment rate
rename tasso_occupaz_comma employ_rate2018
gen employ_rate2018r = employ_rate2018 if employ_rate2018<109.2
replace employ_rate2018r = 100 if employ_rate2018==109.2
sum employ_rate2018r, d
xtile employ_rate2018_dec = employ_rate2018, nq(10)


*************
*Excess death - weighted measures

*using population quintiles 
xtile pop5 = pop2018, nq(5)
gen pop5_1 = pop5/5
gen incr_mar_apr2020_w5 = incr_mar_apr2020*pop5_1
gen incr_sep_oct2020_w5 = incr_sep_oct2020*pop5_1
gen incr_dec_jan2021_w5 = incr_dec_jan2021*pop5_1


****************
*drop municipality of residence for privacy reasons

*individual level data
drop municip municip_mar1 municip_mar
save "Replication\Data\swg_panel_replication", replace

*excess mortality
use "Replication\Data\ISTAT\Tavola riepilogativa_31DICEMBRE_7.903COMUNI_r.dta", clear
drop municip_mar1
save "Replication\Data\ISTAT\Tavola riepilogativa_31DICEMBRE_7.903COMUNI_r_no_municip.dta"

*istat
use "Replication\Privacy\istat01", clear
drop municip_mar
save "Replication\Data\ISTAT\istat01_no_municip", replace

use "Replication\Privacy\istat02", clear
drop municip_mar
save "Replication\Data\ISTAT\istat02_no_municip", replace

*sole 24 ore
use "Replication\Privacy\dati_occupazione_sole24ore_r.dta", clear
drop municip_mar
save "Replication\Data\ISTAT\dati_occupazione_sole24ore_r_no_municip.dta", replace





